package com.caifu.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 网格信息表 Mapper 接口
 * </p>
 *
 * @author Lyf
 * @since 2020-08-08
 */
@Mapper
public interface UtilMapper extends BaseMapper {
    @Select("select auth_no,user_no,org_id,org_level,parent_org_id from sys_user_data_auth where user_no = #{userNo} and type = '1'")
    List<Map<String, String>> getJurisdiction(int userNo) throws Exception;

    @Select("SELECT u2.org_id, u2.org_name,u2.org_level" +
            " FROM( " +
            " SELECT " +
            " @ids AS p_ids," +
            " (SELECT @ids := GROUP_CONCAT(org_id) FROM sys_organization WHERE FIND_IN_SET(parent_org_id, @ids)) AS c_ids," +
            " @l := @l+1 AS LEVEL" +
            " FROM sys_organization,  (SELECT @ids := #{parentNo}, @l := 0 )b" +
            " WHERE @ids IS NOT NULL" +
            " ) u1" +
            " JOIN sys_organization u2" +
            " ON FIND_IN_SET(u2.org_id, u1.p_ids)")
    List<Map<String, String>> getChildNodeByParentNode(@Param("parentNo") int parentNo) throws Exception;

    @Select(" SELECT u2.org_id as \"id\", u2.org_name as \"name\",u2.parent_org_id  as \"parentId\"" +
            " FROM(" +
            " SELECT" +
            " @id c_ids," +
            " (SELECT @id:=GROUP_CONCAT(parent_org_id) FROM sys_organization WHERE FIND_IN_SET(org_id,@id)) p_ids,\n" +
            " @l := @l+1 AS LEVEL" +
            " FROM sys_organization,  (SELECT @id:=#{childrenNo}, @l := 0)b" +
            " WHERE @id IS NOT NULL" +
            " ) u1" +
            " JOIN sys_organization u2  ON u1.c_ids = u2.org_id")
    List<Map<String, String>> getChildNodeByChildrenNode(@Param("childrenNo") int childrenNo) throws Exception;

    @Select(" <script>" +
            " select '1' as user_type,a.user_no,a.user_name,a.user_sex,a.age,b.label_name as household_register,c.org_name as register_org_name,c.org_path as register_area_of_residence,d.org_name as live_org_name,d.org_path as live_area_of_residence,a.live_address,a.register_address from user_info a left join ys_label b on a.household_register = b.label_code left join sys_organization c on a.register_area_of_residence = c.org_id left join sys_organization d on a.live_area_of_residence = d.org_id where 1 = 1" +
            " <if test='orgIds != null and orgIds.size() > 0'>" +
            " and a.belong_organization in (" +
            " <foreach collection='orgIds' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " </if>" +
            " <if test='userName != null and userName != &quot;&quot;'>" +
            " and a.user_name like concat('%',#{userName},'%')" +
            " </if>" +
            " <if test='userPid != null and userPid != &quot;&quot;'>" +
            " and AES_DECRYPT(UNHEX(a.user_pid),'caifu!@#[9921++]') like concat(#{userPid},'%')" +
            " </if>" +
            " and a.status = '1' order by a.live_area_of_residence,a.age,a.user_name,a.user_no limit #{start},#{pageSize}" +
            " </script>")
    List<Map<String, String>> getUserList(@Param("start") int start, @Param("pageSize") int pageSize, @Param("userName") String userName,@Param("userPid") String userPid, @Param("orgIds") List<String> orgIds);

    @Select(" <script>" +
            " select count(*) as count from user_info a left join ys_label b on a.household_register = b.label_code left join sys_organization c on a.register_area_of_residence = c.org_id left join sys_organization d on a.live_area_of_residence = d.org_id where 1 = 1" +
            " <if test='orgIds != null and orgIds.size() > 0'>" +
            " and a.belong_organization in (" +
            " <foreach collection='orgIds' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " </if>" +
            " <if test='userName != null and userName != &quot;&quot;'>" +
            " and a.user_name like concat('%',#{userName},'%')" +
            " </if>" +
            " <if test='userPid != null and userPid != &quot;&quot;'>" +
            " and AES_DECRYPT(UNHEX(a.user_pid),'caifu!@#[9921++]') like concat(#{userPid},'%')" +
            " </if>" +
            " and a.status = '1'" +
            " </script>")
    int getUserListCount(@Param("userName") String userName,@Param("userPid") String userPid, @Param("orgIds") List<String> orgIds);

    //获取养老机构人数
    @Select("SELECT count(*) FROM yl_elderly_info y where y.PENSION_NO=#{orgId}")
    int getElderCountByOrgId(@Param("orgId") String orgId);

    //获取居民人数
    @Select("select count(*) from user_info u where u.belong_organization=#{orgId} or u.register_area_of_residence=#{orgId} or u.live_area_of_residence=#{orgId}")
    int getUserCountByOrgId(@Param("orgId") String orgId);
}